﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanNhanTao.Server.Enum;
using ThanNhanTao.Server.Entities;
using System.Data;

namespace ThanNhanTao.Server
{
   public class PhieuBenhNhanBll
    {
       bvdn_thannhantaoEntities entities;
       public PhieuBenhNhanBll()
       {
           entities = ThanNhanTaoGlobalServer.bvdn_thannhantaoEntities;
       }

       public List<BenhNhan> GetListBenhNhan()
       {
           List<BenhNhan> t = (from c in entities.BenhNhans.Include("Dm_ChanDoan").Include("Dm_DanToc").Include("Dm_NgheNghiep")
                               orderby c.Ten
                               select c).ToList();
               
           return t;
       }
       public int AddBenhNhan(ref BenhNhan benhNhanNew)
       {
           int t = 0;
           try
           {
                BenhNhan temple = new BenhNhan();
                temple.IdBenhNhan = getSoIDMax() + 1;
                temple.Ho = benhNhanNew.Ho;
                temple.Ten = benhNhanNew.Ten;
                temple.Tuoi = benhNhanNew.Tuoi;
                temple.GioiTinh = benhNhanNew.GioiTinh;
                temple.MaNgheNghiep = benhNhanNew.MaNgheNghiep;
                temple.DiaChi = benhNhanNew.DiaChi;
                temple.MaDanToc = benhNhanNew.MaDanToc;
                temple.LaNgoaiTinh = benhNhanNew.LaNgoaiTinh;
                temple.LaBHYT = benhNhanNew.LaBHYT;
                temple.SoBHYT = benhNhanNew.SoBHYT;
                temple.TuNgay = benhNhanNew.TuNgay;
                temple.DenNgay = benhNhanNew.DenNgay;
                temple.NoiGioiThieu = benhNhanNew.NoiGioiThieu;
                temple.IdBenhNhan = benhNhanNew.IdBenhNhan;
                temple.SoNhapVien = benhNhanNew.SoNhapVien;
                temple.NgayVaoVien = benhNhanNew.NgayVaoVien;
                temple.NgayRaVien = benhNhanNew.NgayRaVien;
                temple.NgayDenKhoa = benhNhanNew.NgayDenKhoa;
                temple.ChuyenDen = benhNhanNew.ChuyenDen;
                temple.TinhHinhTuVong = benhNhanNew.TinhHinhTuVong;
                temple.LoaiBenhNhan = benhNhanNew.LoaiBenhNhan;
                temple.LoaiHoSo = benhNhanNew.LoaiHoSo;
                temple.PhuongPhapDieuTri = benhNhanNew.PhuongPhapDieuTri;
                temple.SoLanTHT = benhNhanNew.SoLanTHT;
                temple.SoLanSLM = benhNhanNew.SoLanSLM;
                temple.MLTPE2000 = benhNhanNew.MLTPE2000;
                temple.TuiDungDich = benhNhanNew.TuiDungDich;
                temple.MLPrisma = benhNhanNew.MLPrisma;
                temple.MLPrismaflex = benhNhanNew.MLPrismaflex;
                temple.Hemosol = benhNhanNew.Hemosol;
                temple.HemosolCitrat = benhNhanNew.HemosolCitrat;
                temple.MaChanDoan = benhNhanNew.MaChanDoan;
                temple.GiaiTrinh = benhNhanNew.GiaiTrinh;
                temple.GhiChu = benhNhanNew.GhiChu;
                temple.LoiDan = benhNhanNew.LoiDan;
                temple.NgayChayThanDauTien = benhNhanNew.NgayChayThanDauTien;
                if (benhNhanNew.Dm_ChanDoan != null)
                    temple.Dm_ChanDoanReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_ChanDoan", "MaChanDoan", benhNhanNew.MaChanDoan);
                if (benhNhanNew.Dm_NgheNghiep != null)
                    temple.Dm_NgheNghiepReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_NgheNghiep", "MaNgheNghiep", benhNhanNew.MaNgheNghiep);
                if (benhNhanNew.Dm_DanToc != null)
                    temple.Dm_DanTocReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_DanToc", "MaDanToc", benhNhanNew.MaDanToc);
                entities.AddToBenhNhans(temple);
                entities.SaveChanges();
                benhNhanNew.IdBenhNhan = temple.IdBenhNhan;
                return 1;
           }
           catch (Exception)
           {
               t = -1;
           }
           return t;
       }
       public int UpdateBenhNhan(BenhNhan benhNhanNew)
       {
           try
           {
                BenhNhan temple = entities.BenhNhans.FirstOrDefault(u => u.IdBenhNhan == benhNhanNew.IdBenhNhan);
                temple.Ho = benhNhanNew.Ho;
                temple.Ten = benhNhanNew.Ten;
                temple.Tuoi = benhNhanNew.Tuoi;
                temple.GioiTinh = benhNhanNew.GioiTinh;
                temple.MaNgheNghiep = benhNhanNew.MaNgheNghiep;
                temple.DiaChi = benhNhanNew.DiaChi;
                temple.MaDanToc = benhNhanNew.MaDanToc;
                temple.LaNgoaiTinh = benhNhanNew.LaNgoaiTinh;
                temple.LaBHYT = benhNhanNew.LaBHYT;
                temple.SoBHYT = benhNhanNew.SoBHYT;
                temple.TuNgay = benhNhanNew.TuNgay;
                temple.DenNgay = benhNhanNew.DenNgay;
                temple.NoiGioiThieu = benhNhanNew.NoiGioiThieu;
                temple.IdBenhNhan = benhNhanNew.IdBenhNhan;
                temple.SoNhapVien = benhNhanNew.SoNhapVien;
                temple.NgayVaoVien = benhNhanNew.NgayVaoVien;
                temple.NgayRaVien = benhNhanNew.NgayRaVien;
                temple.NgayDenKhoa = benhNhanNew.NgayDenKhoa;
                temple.ChuyenDen = benhNhanNew.ChuyenDen;
                temple.TinhHinhTuVong = benhNhanNew.TinhHinhTuVong;
                temple.LoaiBenhNhan = benhNhanNew.LoaiBenhNhan;
                temple.LoaiHoSo = benhNhanNew.LoaiHoSo;
                temple.PhuongPhapDieuTri = benhNhanNew.PhuongPhapDieuTri;
                temple.SoLanTHT = benhNhanNew.SoLanTHT;
                temple.SoLanSLM = benhNhanNew.SoLanSLM;
                temple.MLTPE2000 = benhNhanNew.MLTPE2000;
                temple.TuiDungDich = benhNhanNew.TuiDungDich;
                temple.MLPrisma = benhNhanNew.MLPrisma;
                temple.MLPrismaflex = benhNhanNew.MLPrismaflex;
                temple.Hemosol = benhNhanNew.Hemosol;
                temple.HemosolCitrat = benhNhanNew.HemosolCitrat;
                temple.MaChanDoan = benhNhanNew.MaChanDoan;
                temple.GiaiTrinh = benhNhanNew.GiaiTrinh;
                temple.GhiChu = benhNhanNew.GhiChu;
                temple.LoiDan = benhNhanNew.LoiDan;
                temple.NgayChayThanDauTien = benhNhanNew.NgayChayThanDauTien;
                if (benhNhanNew.Dm_ChanDoan != null)
                    temple.Dm_ChanDoanReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_ChanDoan", "MaChanDoan", benhNhanNew.MaChanDoan);
                if (benhNhanNew.Dm_NgheNghiep != null)
                    temple.Dm_NgheNghiepReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_NgheNghiep", "MaNgheNghiep", benhNhanNew.MaNgheNghiep);
                if (benhNhanNew.Dm_DanToc != null)
                    temple.Dm_DanTocReference.EntityKey = new EntityKey("bvdn_thannhantaoEntities.Dm_DanToc", "MaDanToc", benhNhanNew.MaDanToc);
                entities.SaveChanges(true);
                return benhNhanNew.IdBenhNhan;
           }
           catch (Exception)
           {
               return -1;
           }
       }

       public int UpdateColumnNgayChayThan(BenhNhan benhNhanNew)
       {
           try
           {
               BenhNhan benhNhan = entities.BenhNhans.Where(u => u.IdBenhNhan == benhNhanNew.IdBenhNhan).FirstOrDefault();
               benhNhan.HemosolCitrat = benhNhanNew.HemosolCitrat;
               entities.SaveChanges();
               return benhNhanNew.IdBenhNhan;
           }
           catch (Exception)
           {
               return -1;
           }
       }

       public bool DeleteBenhNhan(int idBenhNhanOld)
       {
           try
           {
                   entities.DeleteObject(GetIdBenhNhan(idBenhNhanOld, entities));
                   entities.SaveChanges();
                   return true;
           }
           catch
           {
               return false;
           }
         
       }
       public BenhNhan GetIdBenhNhan(int idBenhNhan,bvdn_thannhantaoEntities benhNhanContext )
       {
           BenhNhan benhNhanId;

           benhNhanId=(from c in benhNhanContext.BenhNhans where c.IdBenhNhan == idBenhNhan select c).SingleOrDefault();

           return benhNhanId;
       }

       public List<Dm_DanToc> GetListDanToc()
       {
           List<Dm_DanToc> t = (from c in entities.Dm_DanToc where c.KichHoat == true select c).ToList();
           return t;
       }
       public List<Dm_NgheNghiep> GetListNgheNghiep()
       {
           List<Dm_NgheNghiep> t = (from c in entities.Dm_NgheNghiep where c.KichHoat == true select c).ToList();
           return t;
       }
       public List<Dm_ChanDoan> GetListChanDoan()
       {
           List<Dm_ChanDoan> t = (from c in entities.Dm_ChanDoan where c.KichHoat == true select c).ToList();
           return t;
       }
       public int getSoIDMax()
       {
           try
           {
                int maxID = entities.BenhNhans.Max(u => u.IdBenhNhan);
                return maxID;
           }
           catch
           {
               return 0;
           }
           //return 0;
       }
       public BenhNhan RetrieveBenhNhanDauTien()
       {
           BenhNhan benhNhan = (from benh in entities.BenhNhans
                                select benh).First();
           return benhNhan;
       }

       public List<BenhNhanEntities> ThongKeBNDungThuoc(DateTime tuNgay, DateTime denNgay, string khu, string maThuoc, int trangThaiBenhNhan)
       {
           DateTime tuNgayConvert = Convert.ToDateTime(Convert.ToDateTime(tuNgay).ToShortDateString() + " 00:00:00");
           DateTime denNgayConvert = Convert.ToDateTime(Convert.ToDateTime(denNgay).ToShortDateString() + " 23:59:59");
           List<BenhNhanEntities> listBenhNhan = new List<BenhNhanEntities>();
           //byte tatCa = EnumTrangThaiBenhNhan.TatCa;
           //byte dangDieuTri = EnumTrangThaiBenhNhan.DangDieuTri;
           //byte raVien = EnumTrangThaiBenhNhan.RaVien;
           listBenhNhan = (from a in entities.BenhNhans
                           join b in entities.ChiDinhs on a.IdBenhNhan equals b.BenhNhan.IdBenhNhan
                           join c in entities.Dm_YeuCau on b.Dm_YeuCau.MaYeuCau equals c.MaYeuCau
                           where (b.NgayYeuCau >= tuNgayConvert && b.NgayYeuCau <= denNgayConvert)
                                 && c.MaYeuCau == maThuoc
                                 && b.ChoNoiTru == false
                                 && (khu.Length == 0 ? true : a.MLPrismaflex == khu)
                                 //&& (trangThaiBenhNhan == tatCa
                                 //      ? (a.NgayVaoVien.Value >= tuNgayConvert && a.NgayVaoVien.Value <= denNgayConvert)
                                 //      : (trangThaiBenhNhan == dangDieuTri
                                 //          ? ((a.NgayRaVien == null && a.NgayVaoVien.Value >= tuNgayConvert
                                 //                                   && a.NgayVaoVien.Value < denNgayConvert)
                                 //               || (a.NgayRaVien != null && a.NgayRaVien.Value >= tuNgayConvert
                                 //                                        && a.NgayRaVien.Value >= denNgayConvert
                                 //            ))
                                 //          : (trangThaiBenhNhan == raVien
                                 //              ? (a.NgayRaVien != null && a.NgayRaVien.Value >= tuNgayConvert
                                 //                                      && a.NgayRaVien.Value <= denNgayConvert
                                 //                )
                                 //              : true)
                                 //        )
                                 //  )
                                 //&& (trangThaiBenhNhan == tatCa
                                 //       ? true
                                 //       : (trangThaiBenhNhan == dangDieuTri
                                 //          ? (a.NgayRaVien == null || (a.NgayRaVien.Value > denNgayConvert && a.NgayVaoVien.Value <= tuNgayConvert))
                                 //          : (trangThaiBenhNhan == raVien
                                 //              ? (a.NgayRaVien != null && a.NgayRaVien.Value <= tuNgayConvert)
                                 //              : true)
                                 //         )
                                 //   )
                           select new BenhNhanEntities
                           {
                               Ho = a.Ho,
                               Ten = a.Ten,
                               GioiTinh = a.GioiTinh,
                               SoNhapVien = a.SoNhapVien,
                               Tuoi = a.Tuoi,
                               Khu = a.MLPrismaflex,
                               SLThuocDung = b.SoLuong
                           }).ToList();
           
           //Group dữ liệu sau cùng
           listBenhNhan = (from a in listBenhNhan
                           group a by new
                           {
                               a.Ho,
                               a.Ten,
                               a.GioiTinh,
                               a.SoNhapVien,
                               a.Tuoi,
                               a.Khu
                           } into entiGroup
                           select new BenhNhanEntities
                           {
                               Ho = entiGroup.Key.Ho,
                               Ten = entiGroup.Key.Ten,
                               GioiTinh = entiGroup.Key.GioiTinh,
                               SoNhapVien = entiGroup.Key.SoNhapVien,
                               Tuoi = entiGroup.Key.Tuoi,
                               Khu = entiGroup.Key.Khu,
                               SLThuocDung = entiGroup.Sum(a=>a.SLThuocDung)
                           }).OrderBy(u=>u.Ten).ToList();

           return listBenhNhan;
       }

       public List<BenhNhanEntities> ThongKeBenhNhan(DateTime tuNgay, DateTime denNgay, string maDKBD, string khu, string nhomMau, byte trangThaiBenhNhan
                                                         , bool? gioiTinh, bool kiemTraDoTuoi, int doTuoiTu, int doTuoiDen, int hoSo, int loaiBenhNhan)
       {
           DateTime tuNgayConvert = Convert.ToDateTime(Convert.ToDateTime(tuNgay).ToShortDateString() + " 00:00:00");
           DateTime denNgayConvert = Convert.ToDateTime(Convert.ToDateTime(denNgay).ToShortDateString() + " 23:59:59");
           List<BenhNhanEntities> listBenhNhan = new List<BenhNhanEntities>();
           byte tatCa = EnumTrangThaiBenhNhan.TatCa;
           byte dangDieuTri = EnumTrangThaiBenhNhan.DangDieuTri;
           byte raVien = EnumTrangThaiBenhNhan.RaVien;
           listBenhNhan = (from a in entities.BenhNhans
                           where (gioiTinh != null ? a.GioiTinh == gioiTinh : true)
                               && (maDKBD.Length == 0 ? true : (a.SoBHYT.Contains(maDKBD)))
                               && (khu.Length == 0 ? true : a.MLPrismaflex == khu)
                               && (nhomMau.Length == 0 ? true : a.MLPrisma == nhomMau)
                               && (kiemTraDoTuoi ? a.Tuoi >= doTuoiTu : true)
                               && (kiemTraDoTuoi ? a.Tuoi <= doTuoiDen : true)
                               && (trangThaiBenhNhan == tatCa ? a.NgayVaoVien.Value <= denNgayConvert : true)
                               && (trangThaiBenhNhan == dangDieuTri ? ((
                                                                        a.NgayRaVien == null && a.NgayVaoVien.Value < denNgayConvert)
                                                                            || (a.NgayRaVien != null && a.NgayVaoVien.Value < denNgayConvert
                                                                                                    && a.NgayRaVien.Value >= denNgayConvert
                                                                      ))
                                                                    : true)
                               && (trangThaiBenhNhan == raVien ? (a.NgayRaVien != null && a.NgayRaVien.Value <= denNgayConvert) : true)
                               && (hoSo > 0 ? a.LoaiHoSo == hoSo : true)
                               && (loaiBenhNhan > 0 ? a.LoaiBenhNhan == loaiBenhNhan : true)
                           select new BenhNhanEntities
                           {
                               Ho = a.Ho,
                               Ten = a.Ten,
                               GioiTinh = a.GioiTinh,
                               SoNhapVien = a.SoNhapVien,
                               Tuoi = a.Tuoi,
                               Khu = a.MLPrismaflex,
                               NhomMau = a.MLPrisma,
                               SoBHYT = a.SoBHYT,
                               NgayVaoVien = a.NgayVaoVien,
                               NgayRaVien = a.NgayRaVien,
                               TinhHinhTuVong = a.TinhHinhTuVong
                           }).OrderBy(u=>u.Ten).ToList();
           
           return listBenhNhan;
       }

       public List<BenhNhanEntities> ThongKeDeNghiThanhToanChiPhiKhamChuaBenhMau25A(DateTime tuNgay, DateTime denNgay, bool thongKeBHYT
                                                                                        , out decimal tongCongTienXN, out decimal tongCongTienThuoc
                                                                                        , out decimal tongCongTienMau, out decimal tongCongTienPTTT
                                                                                        , out decimal tongCongTongCong, out decimal tongCongTienBenhNhanChiTra
                                                                                        , out decimal tongCongTienDeNghiBHYTThanhToan)
       {
           tongCongTienXN = 0;
           tongCongTienThuoc = 0;
           tongCongTienMau = 0;
           tongCongTienPTTT = 0;
           tongCongTongCong = 0;
           tongCongTienBenhNhanChiTra = 0;
           tongCongTienDeNghiBHYTThanhToan = 0;
           string xetNghiem = EnumMaLoaiYeuCau.XetNghiem;
           string thuoc = EnumMaLoaiYeuCau.ThuocDichTruyen;
           string mau = EnumMaLoaiYeuCau.Mau;
           string pttt = EnumMaLoaiYeuCau.PTTT;
           DateTime tuNgayConvert = Convert.ToDateTime(Convert.ToDateTime(tuNgay).ToShortDateString() + " 00:00:00");
           DateTime denNgayConvert = Convert.ToDateTime(Convert.ToDateTime(denNgay).ToShortDateString() + " 23:59:59");
           List<BenhNhanEntities> listBenhNhan = (from k in ((from a in entities.BenhNhans
                                                               join b in entities.ChiDinhs on a.IdBenhNhan equals b.BenhNhan.IdBenhNhan
                                                               join c in entities.Dm_YeuCau on b.Dm_YeuCau.MaYeuCau equals c.MaYeuCau
                                                               where (b.NgayYeuCau >= tuNgayConvert && b.NgayYeuCau <= denNgayConvert)
                                                                        && c.MaLoaiYeuCau == thuoc
                                                                        && (thongKeBHYT ? a.SoBHYT.Length > 0 : (a.SoBHYT.Length == 0 || a.SoBHYT == null)) //Thống kê BHYT thì kiểm tra có BHYT
                                                                        && b.ChoNoiTru == false
                                                               select new BenhNhanEntities
                                                               {
                                                                   Ho = a.Ho,
                                                                   Ten = a.Ten,
                                                                   GioiTinh = a.GioiTinh,
                                                                   Tuoi = a.Tuoi,
                                                                   SoBHYT = a.SoBHYT,
                                                                   MaBenh = a.MaChanDoan,
                                                                   NgayKham = (from d in entities.ChiDinhs
                                                                               orderby d.NgayYeuCau
                                                                                where d.BenhNhan.IdBenhNhan == a.IdBenhNhan
                                                                                        && d.NgayYeuCau >= tuNgayConvert && d.NgayYeuCau <= denNgayConvert
                                                                                select d.NgayYeuCau).FirstOrDefault(),
                                                                   TienXetNghiem = 0,
                                                                   TienThuoc = b.SoLuong * b.GiaYeuCau,
                                                                   TienMau = 0,
                                                                   TienPTTT = 0,
                                                                   TongCong = b.SoLuong * b.GiaYeuCau
                                                               })
                                                               .Concat(
                                                                   from a in entities.BenhNhans
                                                                   join b in entities.ChiDinhs on a.IdBenhNhan equals b.BenhNhan.IdBenhNhan
                                                                   join c in entities.Dm_YeuCau on b.Dm_YeuCau.MaYeuCau equals c.MaYeuCau
                                                                   where (b.NgayYeuCau >= tuNgayConvert && b.NgayYeuCau <= denNgayConvert)
                                                                            && c.MaLoaiYeuCau == xetNghiem
                                                                            && (thongKeBHYT ? a.SoBHYT.Length > 0 : (a.SoBHYT.Length == 0 || a.SoBHYT == null)) //Thống kê BHYT thì kiểm tra có BHYT
                                                                            && b.ChoNoiTru == false
                                                                   select new BenhNhanEntities
                                                                   {
                                                                       Ho = a.Ho,
                                                                       Ten = a.Ten,
                                                                       GioiTinh = a.GioiTinh,
                                                                       Tuoi = a.Tuoi,
                                                                       SoBHYT = a.SoBHYT,
                                                                       MaBenh = a.MaChanDoan,
                                                                       NgayKham = (from d in entities.ChiDinhs
                                                                                   orderby d.NgayYeuCau
                                                                                   where d.BenhNhan.IdBenhNhan == a.IdBenhNhan
                                                                                           && d.NgayYeuCau >= tuNgayConvert && d.NgayYeuCau <= denNgayConvert
                                                                                   select d.NgayYeuCau).FirstOrDefault(),
                                                                       TienXetNghiem = b.SoLuong * b.GiaYeuCau,
                                                                       TienThuoc = 0,
                                                                       TienMau = 0,
                                                                       TienPTTT = 0,
                                                                       TongCong = b.SoLuong * b.GiaYeuCau
                                                                   }
                                                               )
                                                               .Concat(
                                                                    from a in entities.BenhNhans
                                                                    join b in entities.ChiDinhs on a.IdBenhNhan equals b.BenhNhan.IdBenhNhan
                                                                    join c in entities.Dm_YeuCau on b.Dm_YeuCau.MaYeuCau equals c.MaYeuCau
                                                                    where (b.NgayYeuCau >= tuNgayConvert && b.NgayYeuCau <= denNgayConvert)
                                                                            && c.MaLoaiYeuCau == mau
                                                                            && (thongKeBHYT ? a.SoBHYT.Length > 0 : (a.SoBHYT.Length == 0 || a.SoBHYT == null)) //Thống kê BHYT thì kiểm tra có BHYT
                                                                            && b.ChoNoiTru == false
                                                                    select new BenhNhanEntities
                                                                    {
                                                                        Ho = a.Ho,
                                                                        Ten = a.Ten,
                                                                        GioiTinh = a.GioiTinh,
                                                                        Tuoi = a.Tuoi,
                                                                        SoBHYT = a.SoBHYT,
                                                                        MaBenh = a.MaChanDoan,
                                                                        NgayKham = (from d in entities.ChiDinhs
                                                                                    orderby d.NgayYeuCau
                                                                                    where d.BenhNhan.IdBenhNhan == a.IdBenhNhan
                                                                                            && d.NgayYeuCau >= tuNgayConvert && d.NgayYeuCau <= denNgayConvert
                                                                                    select d.NgayYeuCau).FirstOrDefault(),
                                                                        TienXetNghiem = 0,
                                                                        TienThuoc = 0,
                                                                        TienMau = b.SoLuong * b.GiaYeuCau,
                                                                        TienPTTT = 0,
                                                                        TongCong = b.SoLuong * b.GiaYeuCau
                                                                    }
                                                               )
                                                               .Concat(
                                                                    from a in entities.BenhNhans
                                                                    join b in entities.ChiDinhs on a.IdBenhNhan equals b.BenhNhan.IdBenhNhan
                                                                    join c in entities.Dm_YeuCau on b.Dm_YeuCau.MaYeuCau equals c.MaYeuCau
                                                                    where (b.NgayYeuCau >= tuNgayConvert && b.NgayYeuCau <= denNgayConvert)
                                                                            && c.MaLoaiYeuCau == pttt
                                                                            && (thongKeBHYT ? a.SoBHYT.Length > 0 : (a.SoBHYT.Length == 0 || a.SoBHYT == null)) //Thống kê BHYT thì kiểm tra có BHYT
                                                                            && b.ChoNoiTru == false
                                                                    select new BenhNhanEntities
                                                                    {
                                                                        Ho = a.Ho,
                                                                        Ten = a.Ten,
                                                                        GioiTinh = a.GioiTinh,
                                                                        Tuoi = a.Tuoi,
                                                                        SoBHYT = a.SoBHYT,
                                                                        MaBenh = a.MaChanDoan,
                                                                        NgayKham = (from d in entities.ChiDinhs
                                                                                    orderby d.NgayYeuCau
                                                                                    where d.BenhNhan.IdBenhNhan == a.IdBenhNhan
                                                                                            && d.NgayYeuCau >= tuNgayConvert && d.NgayYeuCau <= denNgayConvert
                                                                                    select d.NgayYeuCau).FirstOrDefault(),
                                                                        TienXetNghiem = 0,
                                                                        TienThuoc = 0,
                                                                        TienMau = 0,
                                                                        TienPTTT = b.SoLuong * b.GiaYeuCau,
                                                                        TongCong = b.SoLuong * b.GiaYeuCau
                                                                    }
                                                               ))
                                                        group k by new
                                                        {
                                                            k.Ho,
                                                            k.Ten,
                                                            k.GioiTinh,
                                                            k.Tuoi,
                                                            k.SoBHYT,
                                                            k.MaBenh,
                                                            k.NgayKham
                                                        } into entiGroup
                                                        select new BenhNhanEntities
                                                        {
                                                            Ho = entiGroup.Key.Ho,
                                                            Ten = entiGroup.Key.Ten,
                                                            GioiTinh = entiGroup.Key.GioiTinh,
                                                            Tuoi = entiGroup.Key.Tuoi,
                                                            SoBHYT = entiGroup.Key.SoBHYT,
                                                            MaBenh = entiGroup.Key.MaBenh,
                                                            NgayKham = entiGroup.Key.NgayKham,
                                                            TienXetNghiem = entiGroup.Sum(a => a.TienXetNghiem),
                                                            TienThuoc = entiGroup.Sum(a => a.TienThuoc),
                                                            TienMau = entiGroup.Sum(a => a.TienMau),
                                                            TienPTTT = entiGroup.Sum(a => a.TienPTTT),
                                                            TongCong = entiGroup.Sum(a => a.TongCong)
                                                        }).OrderBy(u => u.Ten).ToList();
           tongCongTienXN = listBenhNhan.Sum(u => u.TienXetNghiem);
           tongCongTienThuoc = listBenhNhan.Sum(u => u.TienThuoc);
           tongCongTienMau = listBenhNhan.Sum(u => u.TienMau);
           tongCongTienPTTT = listBenhNhan.Sum(u => u.TienPTTT);
           tongCongTongCong = listBenhNhan.Sum(u => u.TongCong);
           tongCongTienBenhNhanChiTra = listBenhNhan.Sum(u => u.TienNguoiBenhChiTra);
           tongCongTienDeNghiBHYTThanhToan = listBenhNhan.Sum(u => u.TienDeNghiBHYTThanhToan);
           return listBenhNhan;
       }

       public void LanChayDauTienVaSauCung(int idBenhNhan, out int soLanDaChay, out ChayThanNhanTao chayLanDau, out ChayThanNhanTao chayLanCuoi)
       {
           soLanDaChay = 0;
           // get lan chay dau tien
           chayLanDau = (from c in entities.ChayThanNhanTaos
                            where c.IdBenhNhan == idBenhNhan
                                  && c.SoLan == (entities.ChayThanNhanTaos.Where(d => d.IdBenhNhan == idBenhNhan).Min(e => e.SoLan))
                            select c).FirstOrDefault<ChayThanNhanTao>();
           //get lan chay sau cung
           chayLanCuoi = (from c in entities.ChayThanNhanTaos
                                         where c.IdBenhNhan == idBenhNhan
                                               && c.SoLan == (entities.ChayThanNhanTaos.Where(d => d.IdBenhNhan == idBenhNhan).Max(e => e.SoLan))
                                         select c).FirstOrDefault<ChayThanNhanTao>();
           if (chayLanDau!=null && chayLanCuoi !=null)
           {
               //so lan da chay
               //soLanDaChay = (chayLanCuoi.SoLan - chayLanDau.SoLan) + 1;
               soLanDaChay = entities.ChayThanNhanTaos.Where(d => d.IdBenhNhan == idBenhNhan).Count();
           }
       }
    }
}
